package com.swinginwind.owf.leave;

public class SqlStatements {
	
	public static String BASE_ITEM_KEY_SQL = "select to_char(OWF_SEQ.nextval) seq from dual";
	
	public static String BASE_ITEM_QUERY_SQL = 
			"select t1.item_type,\n" + 
					"       t1.begin_date,\n" + 
					"       t1.end_date,\n" + 
					"       t4.*,\n" + 
					"		t2.assigned_user,\n" +
					"       t2.activity_name" +
					"  from wf_items t1\n" + 
					"  left join (select item_key,\n" + 
					"                    max(decode(t5.name, 'MANAGER', t5.text_value)) manager,\n" + 
					"                    max(decode(t5.name, 'EMPLOYEE', t5.text_value)) employee,\n" + 
					"                    max(decode(t5.name, 'START_DATE', t5.text_value)) app_start_date,\n" + 
					"                    max(decode(t5.name, 'END_DATE', t5.text_value)) app_end_date,\n" + 
					"                    max(decode(t5.name, 'ORDER_ID', t5.text_value)) order_id,\n" + 
					"                    max(decode(t5.name, 'DESC', t5.text_value)) \"DESC\",\n" + 
					"                    max(decode(t5.name, 'COMMENTS', t5.text_value)) \"COMMENTS\",\n" + 
					"                    max(decode(t5.name, 'STATUS', t5.text_value)) \"STATUS\",\n" + 
					"                    max(decode(t5.name, 'APP_DATE', t5.text_value)) app_date\n" + 
					"               from WF_ITEM_ATTRIBUTE_VALUES t5\n" + 
					"              group by t5.item_key) t4\n" + 
					"    on t4.item_key = t1.item_key\n" + 
					"  left join (SELECT *\n" + 
					"               FROM (SELECT z.assigned_user,\n" + 
					"                            z.item_key,\n" + 
					"                            p.activity_name,\n" + 
					"                            ROW_NUMBER() OVER(PARTITION BY z.item_key ORDER BY z.begin_date desc,z.execution_time desc) AS code_id\n" + 
					"                       FROM wf_item_activity_statuses z\n" + 
					"                       left join wf_process_activities p\n" + 
					"                         on z.process_activity = p.instance_id)\n" + 
					"              WHERE code_id = 1) t2\n" + 
					"    on t1.item_key = t2.item_key\n" + 
					" where t1.item_type = 'GM_DEMO' order by begin_date desc";

}
